﻿Imports System.Data.OleDb
Public Class hotelDetail
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        'Make this contant visible to superusers
        If User.IsInRole("superuser") Then
            ph_superusers.Visible = True
        Else
            'hide from non superusers
            ph_superusers.Visible = False

        End If

        'Check if the user has arrived from a different page before running following code
        If IsPostBack = False Then

            'If someone comes to this page with no ID in the URL, send them back to the hotel list page
            If Request.Params("ID") = "" Then Response.Redirect("hotellist.aspx")

            'Create database connection (can view connection string name form web.config)
            Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("hotelsConnectionString").ConnectionString)

            'Read from the database - "Select something from tablename where something = something"
            Dim SqlString As String = "Select * from Comments where Hotel_fkid=@f1"

            'Protect against sql injection attacks such as mass deletion or destruction of databaseDim cmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
            Dim cmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
            cmd.CommandType = CommandType.Text

            'Get the information from the query string
            cmd.Parameters.AddWithValue("@f1", Request.Params("ID"))

            'Read data from the database i.e. dataReader
            oleDbConn.Open()
            Dim dataReader = cmd.ExecuteReader()

            'What will fill the gridview
            GridView2.DataSource = dataReader
            GridView2.DataBind()

            'See what hotels the traveller has viewed
            Dim hotelsViewedSql As String = "Insert into HotelsViewed(Traveller,Hotel_fkID) Values (@f1,@f2)"


            'Protect against sql injection attacks such as mass deletion or destruction of databaseDim cmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
            Dim cmd2 As OleDbCommand = New OleDbCommand(hotelsViewedSql, oleDbConn)
            cmd2.CommandType = CommandType.Text

            'Get the information from the query string
            cmd2.Parameters.AddWithValue("@f1", User.Identity.Name)
            cmd2.Parameters.AddWithValue("@f2", Request.Params("ID"))
            cmd2.ExecuteNonQuery()

        End If

    End Sub

    Protected Sub btn_addComment_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_addComment.Click

        'Create database connection (can view connection string name form web.config)
        Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("hotelsConnectionString").ConnectionString)

        'Create the sql string that will be used to add records to a database Syntax - "Insert into tablename(list of fields sperated by commas) values (list of values (aliases))"
        Dim SqlString As String = "Insert into Comments(Author,Title,Comment,hotel_fkid) Values (@f1,@f2,@f3,@f4)"

        'Protect against sql injection attacks such as mass deletion or destruction of database 
        Dim cmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
        cmd.CommandType = CommandType.Text

        'get the user's name from their log on
        cmd.Parameters.AddWithValue("@f1", User.Identity.Name)

        'add the other parameters
        cmd.Parameters.AddWithValue("@f2", tb_title.Text)
        cmd.Parameters.AddWithValue("@f3", tb_comment.Text)
        cmd.Parameters.AddWithValue("@f4", Request.Params("ID"))

        'Open the database connection
        oleDbConn.Open()

        'Don't read anything, just run a command
        cmd.ExecuteNonQuery()

        'fill grid view 2 - similar to code above
        Dim fillComments As String = "Select * from Comments where Hotel_fkid=@f1"
        Dim fillCommentsCmd As OleDbCommand = New OleDbCommand(fillComments, oleDbConn)
        fillCommentsCmd.CommandType = CommandType.Text
        fillCommentsCmd.Parameters.AddWithValue("@f1", Request.Params("ID"))

        'Read data from the database i.e. dataReader
        Dim dataReader = fillCommentsCmd.ExecuteReader()

        'What will fill the gridview
        GridView2.DataSource = dataReader
        GridView2.DataBind()

        'Clear the users entries
        tb_title.Text = ""
        tb_comment.Text = ""

        'Redirect the user to the comment confirmation page
        'Response.Redirect("commentConfirmation.aspx")
    End Sub

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
        'Create database connection (can view connection string name form web.config)
        Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("hotelsConnectionString").ConnectionString)

        'Create the sql string that will be used to add records to a database Syntax - "Insert into tablename(list of fields sperated by commas) values (list of values (aliases))"
        Dim SqlString As String = "Insert into hotelTagJunction(hotel_fkid, tag_fkid) Values (@f1,@f2)"

        'Open the database connection
        oleDbConn.Open()

        'Loop through all the items in the checkbox list to find the selected ones
        For Each cb As ListItem In CheckBoxList1.Items



            'get the value from those selected
            If cb.Selected Then
                'associate it with the ID of the hotel 
                Dim cmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
                cmd.CommandType = CommandType.Text
                'get the hotel ID from the query string
                cmd.Parameters.AddWithValue("@f1", Request.Params("ID"))

                'get the tag from the selected checkbox
                cmd.Parameters.AddWithValue("@f2", cb.Value)

                'Don't get any data back
                cmd.ExecuteNonQuery()


            End If
        Next

        'Stop the code running if nothing is entered in the tag textbox
        If TextBox1.Text = "" Then Return

        'Split the entered tags into an array of strings
        Dim newTags = TextBox1.Text.Split(",")

        'Look at each tag
        For Each newTag In newTags

            'Add the new tag to the tag table
            Dim newTagSql As String = "Insert into Tags(tag) values (@f1)"

            'associate it with the ID of the hotel 
            Dim cmd As OleDbCommand = New OleDbCommand(newTagSql, oleDbConn)
            cmd.CommandType = CommandType.Text

            'get the hotel ID from the query string
            cmd.Parameters.AddWithValue("@f1", newTag)

            'Don't get any data back
            cmd.ExecuteNonQuery()

            'find the ID of the new tag by seeing what was last added to the database, where the records have been added in descending order, i.e. the one with the highest ID
            Dim getLastIdCmd As OleDbCommand = New OleDbCommand("Select top 1 ID from Tags order by ID desc", oleDbConn)

            'Show the ID of the last tag added
            Dim dr = getLastIdCmd.ExecuteReader()
            dr.Read()
            Dim lastId = dr.GetValue(0)

            'Associate this witht he hotel ID

            Dim newcmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
            newcmd.CommandType = CommandType.Text
            newcmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
            newcmd.Parameters.AddWithValue("@f2", lastId)

            'Don't get any data back
            newcmd.ExecuteNonQuery()

        Next


    End Sub

    Protected Sub tb_comment_TextChanged(ByVal sender As Object, ByVal e As EventArgs) Handles tb_comment.TextChanged

    End Sub

    Protected Sub CheckBoxList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles CheckBoxList1.SelectedIndexChanged

    End Sub
End Class